import java.io.*;
import java.sql.*;

import javax.servlet.*;
import javax.servlet.http.*;

public class SearchItem extends HttpServlet
{
	protected Connection con;
	protected String dbsource = "jdbc:mysql://localhost/inventory"; 
	protected String drv = "com.mysql.jdbc.Driver";
	protected PreparedStatement ps;
	protected String uvar = null;
	PrintWriter out;
	
	public void doGet(HttpServletRequest req, HttpServletResponse res)throws ServletException, IOException
	{
		res.setContentType("text/html");
		PrintWriter out = res.getWriter();
		
		out.println("<html><body>");
		out.print("<link rel=\"stylesheet\" type=\"text/css\" href=\"style.css\" />");
		out.print("<div id=\"outer\">"
				+" <div id=\"wrapper\">"
				+" <div id=\"nav\">"
				+" <div id=\"nav-left\">"
				+" <div id=\"nav-right\">");
		out.print("<ul>"
            +"<li><a href=AddItem>Add New Item</a</li>"
            +"<li><a href=AddCategory>Add New Category</a></li>"
            +"<li><a href=InventoryList>Search</a></li>"
            +" </ul>");
		out.print("</div>");
		out.print("</div>");
		out.print("<div class=\"clear\"></div>");
		out.print("</div>");
		out.print("<div id=\"head-2\"></div>"
				+"<div id=\"login\">"
				+"<div id=\"login-bot\">");
		
		
		out.println("<form action=\"" +
				"\" method=\"post\">" +
				"<h1><center>SEARCH ITEM</center></h1>" +
				"<h3><center>SF Online Inventory System</center><h3>" +
				"<table align=\"center\" border=\"1\">" +
				"<tr><td><font face=\"Trebuchet MS\" color=\"red\">SEARCH ITEMS</font></td>" +
				"<tr><td>Search only: </td> " +
				"<td><select name=\"query1\">" +
				     "<option selected>Select</option>" +
				     "<option value=\"item_code\">Item Code</option>" +
				     "<option value=\"item_name\">Item Name</option>" +
				     "<option value=\"item_detail\">Item Detail</option>" +
				     "<option value=\"category_code\">Category Code</option></td></tr>" +
				"<tr><td>Value:</td>" +
				"<td><input type=\"text\" name=\"queryValue1\"></td></tr>" +
				"<tr align=\"center\"><td><input type=\"submit\" value=\"Search\"></td>" +
				"<tr><td>View all contents</td>" +
				"</table>" +
				"</form> "+
				
				"<p>" +
				"<form action=\"" +
				"\" method=\"post\">" +
				"<table align=\"center\" border=\1\">" +
				"<tr><td><font face=\"Trebuchet MS\" color=\"red\">SEARCH CATEGORY</font></td>" +
				"<tr><td>Search only: </td>" +
				"<td><select name=\"query2\">" +
				      "<option selected>Select</option>" +
				      "<option value=\"category_code\">Category Code</option>" +
				      "<option value=\"category_name\">Category Name</option>" +
				      "<option value=\"subcategory_code\">Subcategory Code</option></td></tr>" +
				"<tr><td>Value:</td>" +
				"<td><input type=\"text\" name=\"queryValue2\"></td></tr>" +
				"<tr align=\"center\"><td><input type=\"submit\" value=\"Search\"></td>" +
				"</table></form></body></html>");
		out.close();
	}
	
	public void doPost(HttpServletRequest req, HttpServletResponse res)throws ServletException, IOException 
    {
		res.setContentType("text/html");
    	PrintWriter out = res.getWriter();
    	
    	out.println("<html><body>");
    	
    	String itemQuery = req.getParameter("query1");
    	String itemQueryValue = req.getParameter("queryValue1");
    	String categoryQuery = req.getParameter("query2");
    	String categoryQueryValue = req.getParameter("queryValue2");
    	try
    	{
    		String query;
    		String string1 = null;
    		String string2 = null;
    		String string3 = null;
    		String string4 = null;
    		String string5 = null;
    		String string6 = null;
        	
        	if (itemQuery != null && itemQueryValue != null)
        	{
    			query = ("Select '" +  itemQuery + "'" + "from item where '" + itemQuery + "'" + "like '" + itemQueryValue + "'");
    			Class.forName(drv);
    			con = DriverManager.getConnection(dbsource, "root", "root");
    			Statement stmt = con.createStatement();
    			ResultSet result = stmt.executeQuery(query);
    			
    			out.println("<html><head><title>Search Item</title></head>");
    			out.print("<link rel=\"stylesheet\" type=\"text/css\" href=\"style.css\" />");
    			out.print("<div id=\"outer\">"
    					+" <div id=\"wrapper\">"
    					+" <div id=\"nav\">"
    					+" <div id=\"nav-left\">"
    					+" <div id=\"nav-right\">");
    			out.print("<ul>"
    	            +"<li><a href=AddItem>Add New Item</a</li>"
    	            +"<li><a href=AddCategory>Add New Category</a></li>"
    	            +"<li><a href=InventoryList>Search</a></li>"
    	            +" </ul>");
    			out.print("</div>");
    			out.print("</div>");
    			out.print("<div class=\"clear\"></div>");
    			out.print("</div>");
    			out.print("<div id=\"head-2\"></div>"
    					+"<div id=\"login\">"
    					+"<div id=\"login-bot\">");
    			
    			
    			out.println("<table class=\"sortable\" id\"sortable_example\">" +
    					"<tr><th>Item Code</th><th>Item Name</th><th>Item Detail</th><th>Category Code</th><th>Quantity</th></tr>");
    					while(result.next()) 
    					{
    						string1 = result.getString(1).trim();//item table PK
    						string2 = result.getString(2).trim();
    						string3 = result.getString(3).trim();
    						string4 = result.getString(4).trim();
    						string5 = result.getString(5).trim();
    						string6 = result.getString(6).trim();
    						out.println("<tr><td>"+ string2 +"</td>");
    						out.println("<td>" +string3 + "</td>");
    						out.println("<td>" +string4 + "</td>");
    						out.println("<td>" +string5 + "</td>");
    						out.println("<td>" +string6 + "</td></tr>");
    					}
        	}
    		if (categoryQuery != null && categoryQueryValue != null)
    			query = ("Select '" + categoryQuery + "'" + "from category where '" + categoryQuery + "'" + "= '" + categoryQueryValue + "'");
    	}
    	catch (Exception e)
		{
			out.println("<html><body background color=red>"+e+"</body></html>");
		}
    	
    }
}
